package example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import pojo.Brand;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class BrandTest {
    @Test
    public void testSelectAll() throws Exception {
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection connection = dataSource.getConnection();

        String sql = "select *from tb_brand;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        List<Brand> brands = new ArrayList<>();
        Brand brand = null;
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String brand_name = resultSet.getString("brand_name");
            String company_name = resultSet.getString("company_name");
            String description = resultSet.getString("description");
            int ordered = resultSet.getInt("ordered");
            int status = resultSet.getInt("status");
            brand = new Brand();
            brand.setId(id);
            brand.setBrand_name(brand_name);
            brand.setCompany_name(company_name);
            brand.setDescription(description);
            brand.setOrdered(ordered);
            brand.setStatus(status);
            brands.add(brand);
        }
        System.out.println(brands);
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void insertsql() throws Exception {
        String brandName = "香飘飘";
        String comapnyName = "香飘飘";
        int orded = 1;
        String description = "无";
        int status = 1;
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection connection = dataSource.getConnection();

        String sql = "insert into  tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?);";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, brandName);
        preparedStatement.setString(2, comapnyName);
        preparedStatement.setInt(3, orded);
        preparedStatement.setString(4, description);
        preparedStatement.setInt(5, status);

        int count = preparedStatement.executeUpdate();
        boolean flag = count > 0;
        System.out.println(count);
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void updatesql() throws Exception {
        String brandName = "香飘飘1";
        String comapnyName = "香飘飘1";
        int orded = 11;
        String description = "无1";
        int status = 0;
        int id=4;
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection connection = dataSource.getConnection();

        String sql = "update  tb_brand set brand_name=?,company_name=?,ordered=?,description=?,status=? where id=?";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, brandName);
        preparedStatement.setString(2, comapnyName);
        preparedStatement.setInt(3, orded);
        preparedStatement.setString(4, description);
        preparedStatement.setInt(5, status);
        preparedStatement.setInt(6,id);

        int count = preparedStatement.executeUpdate();
        boolean flag = count > 0;
        System.out.println(count);
        preparedStatement.close();
        connection.close();
    }


    @Test
    public void deletesql() throws Exception {
        String brandName = "香飘飘1";
        String comapnyName = "香飘飘1";
        int orded = 11;
        String description = "无1";
        int status = 0;
        int id=4;
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection connection = dataSource.getConnection();

        String sql = "delete from tb_brand where id=?";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);


        int count = preparedStatement.executeUpdate();
        boolean flag = count > 0;
        System.out.println(count);
        preparedStatement.close();
        connection.close();
    }
}